/**
 * Created by user on 2015/11/11.
 */

var async = require('async');
var moment = require('moment');
var mysqlUtil = require('../../../common/utils/mysqlUtil');
var User = mongoose.model('user');


//用户情值
exports.userScoreListService = function(req ,callback) {

    var body = req.body;
    var page = req.params.page;
    var size = Number(req.params.size);
    var index = (Number(req.params.page) - 1) * size;
    var totalPage = 0;

    var option = clone(body);
    delete option['communityName'];
    var sortType = {dateCreated:1};

    if (option.mobilePhone) option.mobilePhone = new RegExp(option.mobilePhone);
    if (option.nickName) option.nickName = new RegExp(option.nickName);

    var data = null;

    logger.debug(option);

    async.series([
        function (callbackInner) {
            User.count(option, function (err, count) {

                totalPage = Math.ceil(count/size);
                var temp = {count: count, totalPage:totalPage};
                callbackInner(err, temp);

            });
        },

        function(callbackInner) {
            var conditionList = [];

            // for future developers: like is more optimized than regexp
            if (body._id) {
                conditionList.push("u._id = '{0}'".format(body._id));
            }
            if (body.nickName) {
                conditionList.push("u.nickName like '%{0}%'".format(body.nickName));
            }
            if (body.mobilePhone) {
                conditionList.push("u.mobilePhone like '{0}%'".format(body.mobilePhone));
            }
            if (body.communityName) {
                conditionList.push("c.communityName like '%{0}%'".format(body.communityName));
            }

            var sqlConditions = sqlConditionString(conditionList);

            var sqlQuery = commentToString(function() {
            /*!
                 SELECT
                    nickName, u._id objId, mobilePhone, roomUnit, roomNumber, communityName,
                    total_value totalValue, current_value currentValue
                 FROM
                    userAll u inner join community c on u.community = c._id
                    inner join score_info score on score.user_id = u._id
                    {0}
                 ORDER BY current_value DESC
                 LIMIT {1}, {2}
             */
            }).format(sqlConditions, index, size);

            console.log(sqlQuery);

            mysqlUtil.queryBI(sqlQuery, function (err, result) {
                callbackInner(err, result);
            });
        },

        /*
        function (callbackInner) {

            User.find(option, 'nickName mobilePhone roomUnit roomNumber community')
                .sort(sortType).skip(index).limit(size)
                .populate('community', 'communityName').exec(function (err, user) {
                    //logger.info(user);
                    var i = 0, length = user.length, data = new Array(length);
                    if(user.length>0) {

                        async.each(user, function (item, callbackInner2) {

                            var sql = 'SELECT total_value,current_value FROM score_info WHERE user_id=?';
                                mysqlUtil.query(sql, [item._id + ""], function (err, result) {

                                    if (result != null && result.length > 0) {
                                        item._doc.totalValue = result[0].total_value;
                                        item._doc.currentValue = result[0].current_value;
                                    };
                                    callbackInner2(null,item);
                                });

                        }, function (err) {
                            callbackInner(err, user);
                        });

                    }else{
                        callbackInner(null, {});
                    }

                });
        }
        */

    ], callback);

};


exports.userScoreDetailService = function(req ,callback) {

    var body = req.body;
    var page = req.params.page;
    var size = Number(req.params.size);
    var index = (Number(req.params.page) - 1) * size;
    var totalPage = 0;

    //var option = {};
    //var sortType = {dateCreated:-1};

    //if (body.startDate || body.endDate) {
    //    if (body.startDate && body.endDate) {
    //        if (body.startDate < body.endDate) {
    //            option.releaseDate = {$gte: body.startDate, $lte: body.endDate};
    //        }
    //    } else if (body.startDate) {
    //        option.releaseDate = {$gte: body.startDate};
    //    } else if (body.endDate) {
    //        option.releaseDate = {$lte: body.endDate};
    //    };
    //};

    //var sql = "SELECT change_type, change_value, change_reason, CASE when update_time > 10000000000 then update_time else update_time * 1000 end as time FROM score_sn WHERE user_id=? order by time desc";
    //
    //var data = null;

    logger.debug(body);

    async.series([
        function (callbackInner) {
            var sql = "SELECT count(*) count FROM score_sn WHERE user_id='"+ body.userId +"'";
            if(body.startDate || body.endDate){
                if(body.startDate && !body.endDate){
                    var timestamp = Date.parse(new Date(body.startDate));
                    sql += 'and (CASE when update_time > 10000000000 then update_time else update_time * 1000 end)>='+ timestamp;
                };
                if(!body.startDate && body.endDate){
                    var timestamp = Date.parse(new Date(body.endDate));
                    sql += 'and (CASE when update_time > 10000000000 then update_time else update_time * 1000 end)<='+ timestamp;
                };
                if(body.startDate && body.endDate){
                    var timestamp = Date.parse(new Date(body.startDate));
                    var timestamp2 = Date.parse(new Date(body.endDate));
                    sql += 'and( ';
                    sql += '(CASE when update_time > 10000000000 then update_time else update_time * 1000 end)>= '+ timestamp+' and ';
                    sql += '(CASE when update_time > 10000000000 then update_time else update_time * 1000 end)<= '+ timestamp2;
                    sql += ' ) ';
                };

            };
            sql += " order by update_time desc";
            logger.debug(sql);
            mysqlUtil.query(sql, function (err, result) {

                totalPage = Math.ceil(result[0].count/size);
                var temp = {count: result[0].count, totalPage:totalPage};
                callbackInner(err, temp);

            });

        },
        function (callbackInner) {

            var sql = "SELECT change_type, change_value, change_reason, CASE when update_time > 10000000000 then update_time else update_time * 1000 end as time FROM score_sn WHERE user_id='"+ body.userId +"'";
            if(body.startDate || body.endDate){
                if(body.startDate && !body.endDate){
                    var timestamp = Date.parse(new Date(body.startDate));
                    sql += 'and (CASE when update_time > 10000000000 then update_time else update_time * 1000 end)>='+ timestamp;
                };
                if(!body.startDate && body.endDate){
                    var timestamp = Date.parse(new Date(body.endDate));
                    sql += 'and (CASE when update_time > 10000000000 then update_time else update_time * 1000 end)<='+ timestamp;
                };
                if(body.startDate && body.endDate){
                    var timestamp = Date.parse(new Date(body.startDate));
                    var timestamp2 = Date.parse(new Date(body.endDate));
                    sql += 'and( ';
                    sql += '(CASE when update_time > 10000000000 then update_time else update_time * 1000 end)>= '+ timestamp+' and ';
                    sql += '(CASE when update_time > 10000000000 then update_time else update_time * 1000 end)<= '+ timestamp2;
                    sql += ' ) ';
                };

            };
            sql += " order by time desc";
            sql += " limit "+index +','+size;
            logger.debug(sql);
            mysqlUtil.query(sql, function (err, result) {
                callbackInner(err, result);

            });

        }
    ], callback);

};

function commentToString(f) {
    return f.toString().
        replace(/^[^\/]+\/\*!?/, '').
        replace(/\*\/[^\/]+$/, '');
}

function sqlConditionString(conditionList) {
    var len = conditionList.length
    if (len == 0)
        return ''
    var condition = "where "

    for (var i = 0; i < len; i++) {
        condition += conditionList[i]
        if (i != len - 1)
            condition += ' and '
    }
    return condition
}

function clone(obj) {
    if (obj == null || typeof(obj) != 'object')
        return obj;

    var temp = new obj.constructor();
    for (var key in obj)
        temp[key] = clone(obj[key]);

    return temp;
}